package org.iteam.core.service.base;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.iteam.core.Constant;
import org.iteam.core.service.BaseService;
import org.iteam.core.service.DBService;
import org.iteam.core.service.mysql.impl.base.TreeNodeServiceImpl;
import org.iteam.core.view.TreeNode;

public class TreeNodeService extends BaseService<TreeNode> {

	private static TreeNodeService serviceMysql = null;
	private static TreeNodeService serviceOracle = null;

	public static TreeNodeService getInstance() {
		Constant.DataBaseType dbTypes = Constant.dataBaseType;
		if (dbTypes.equals(Constant.DataBaseType.MYSQL)) {
			return getMySqlInstance();
		} else if (dbTypes.equals(Constant.DataBaseType.ORACLE)) {
			return getOracleInstance();
		}
		return null;
	}

	public static TreeNodeService getMySqlInstance() {
		if (serviceMysql == null)
			serviceMysql = new TreeNodeServiceImpl();
		return serviceMysql;
	}

	public static TreeNodeService getOracleInstance() {
		if (serviceOracle == null)
			serviceOracle = new org.iteam.core.service.oracle.impl.base.TreeNodeServiceImpl();
		return serviceOracle;
	}

	public TreeNodeService() {
		super(TreeNode.class);
		// TODO Auto-generated constructor stub
	}

	public List<TreeNode> getArea() throws SQLException {
		String sql = "SELECT B.AID AS ID, B.\"NAME\" AS TEXT, B.PID AS PARENTID, ( SELECT COUNT (1) FROM BASE_AREA A WHERE A .PID = B.AID ) AS COUNT FROM BASE_AREA B WHERE B.PID IS NULL ORDER BY B.AID";
		Connection conn = DBService.getDataSource().getConnection();
		List<TreeNode> TreeNodeViewlist = null;
		try {
			List<TreeNode> list = super.query(conn, TreeNode.class, sql);
			TreeNodeViewlist = new ArrayList<TreeNode>();
			for (TreeNode area : list) {
				if (area.getCount() > 0) {
					area.setChildren(getArea(conn, area.getId()));
					if (area.getChildren() != null
							&& area.getChildren().size() > 0) {
						area.setState("closed");
					}
				}
				TreeNodeViewlist.add(area);
			}

		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (conn != null && !conn.isClosed())
				conn.close();
		}
		return TreeNodeViewlist;
	}

	private List<TreeNode> getArea(Connection conn, String pid)
			throws SQLException {
		String sql = "SELECT B.AID AS ID, B.\"NAME\" AS TEXT, B.PID AS PARENTID, ( SELECT COUNT (1) FROM BASE_AREA A WHERE A .PID = B.AID ) AS COUNT FROM BASE_AREA B WHERE B.PID = ? ORDER BY B.AID";
		List<TreeNode> TreeNodeViewlist = null;
		try {
			List<TreeNode> list = super.query(conn, TreeNode.class, sql, pid);
			TreeNodeViewlist = new ArrayList<TreeNode>();
			for (TreeNode area : list) {
				if (area.getCount() > 0) {
					area.setChildren(getArea(conn, area.getId()));
					if (area.getChildren() != null
							&& area.getChildren().size() > 0) {
						area.setState("closed");
					}
				}
				TreeNodeViewlist.add(area);
			}

		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
		}
		return TreeNodeViewlist;
	}

	public List<TreeNode> getIndustry() throws SQLException {
		String sql = "SELECT B.CODE AS ID, B.\"NAME\" AS TEXT, B.\"PARENT\" AS PARENTID, ( SELECT COUNT (1) FROM BASE_INDUSTRY A WHERE A .\"PARENT\" = B.CODE ) AS COUNT FROM BASE_INDUSTRY B WHERE B.\"PARENT\" IS NULL ORDER BY B.CODE";
		Connection conn = DBService.getDataSource().getConnection();
		List<TreeNode> TreeNodeViewlist = null;
		try {
			List<TreeNode> list = super.query(conn, TreeNode.class, sql);
			TreeNodeViewlist = new ArrayList<TreeNode>();
			for (TreeNode area : list) {
				if (area.getCount() > 0) {
					area.setChildren(getIndustry(conn, area.getId()));
					if (area.getChildren() != null
							&& area.getChildren().size() > 0) {
						area.setState("closed");
					}
				}
				TreeNodeViewlist.add(area);
			}

		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
			if (conn != null && !conn.isClosed())
				conn.close();
		}
		return TreeNodeViewlist;
	}

	private List<TreeNode> getIndustry(Connection conn, String pid)
			throws SQLException {
		String sql = "SELECT B.CODE AS ID, B.\"NAME\" AS TEXT, B.\"PARENT\" AS PARENTID, ( SELECT COUNT (1) FROM BASE_INDUSTRY A WHERE A .\"PARENT\" = B.CODE ) AS COUNT FROM BASE_INDUSTRY B WHERE B.\"PARENT\"  = ? ORDER BY B.CODE";
		List<TreeNode> TreeNodeViewlist = null;
		try {
			List<TreeNode> list = super.query(conn, TreeNode.class, sql, pid);
			TreeNodeViewlist = new ArrayList<TreeNode>();
			for (TreeNode area : list) {
				if (area.getCount() > 0) {
					area.setChildren(getIndustry(conn, area.getId()));
					if (area.getChildren() != null
							&& area.getChildren().size() > 0) {
						area.setState("closed");
					}
				}
				TreeNodeViewlist.add(area);
			}

		} catch (Exception e) {
			throw new SQLException(e.getMessage());
		} finally {
		}
		return TreeNodeViewlist;
	}
}
